"""
Friendship相关处理逻辑
"""


def get_friendship(db, logger, data: dict):
    try:
        # 根据用户mac地址查询用户信息
        user_info = db.get_one("User", {"mac_addr": data["mac_addr"]})

        # 查询好友列表
        friendship_wheres = ["fp.master_id = ?"]
        friendship_params = [user_info["id"]]

        user_team_wheres = ["ut.user_id = ?"]
        user_team_params = [user_info["id"]]
        if data["name"] and data["name"].strip():
            friendship_wheres.append("u.name LIKE ?")
            friendship_params.append(f"%{data['name'].strip()}%")

            user_team_wheres.append("t.name LIKE ?")
            user_team_params.append(f"%{data['name'].strip()}%")

        friendship_sql = f"""
        SELECT fp.*, u.*
        FROM Friendship fp
        JOIN User u ON fp.user_id = u.id
        WHERE {" AND ".join(friendship_wheres)}
        """

        friends_list = db.execute_query(friendship_sql, tuple(friendship_params))
        # 获取未读信息列表
        for index, item in enumerate(friends_list):
            unread_count = db.get_count(
                "Message", {"source": item["mac_addr"], "target": user_info["mac_addr"], "status": "unread"}
            )
            friends_list[index]["unread"] = unread_count
            friends_list[index]["category"] = "user"
            friends_list[index]["_id"] = f"user_{item['id']}"

        # 查询该用户所属的团队列表
        user_teams_sql = f"""
        SELECT ut.*, t.*
        FROM User_Team ut
        JOIN Team t ON ut.team_id = t.id
        WHERE {" AND ".join(user_team_wheres)}
        """
        user_teams = db.execute_query(user_teams_sql, tuple(user_team_params))
        teams = [db.get_one("Team", {"id": team["team_id"]}) for team in user_teams]

        for index, item in enumerate(teams):
            teams[index]["category"] = "team"

            team_msg_count = db.get_count(
                "Team_Message",
                {
                    "team_id": item["id"],
                },
            )

            user_read_count = db.get_count(
                "Team_Message_Read",
                {
                    "team_id": item["id"],
                    "user_id": user_info["id"],
                },
            )

            teams[index]["unread"] = team_msg_count - user_read_count
            teams[index]["users"] = db.get_count(
                "User_Team",
                {
                    "team_id": item["id"],
                },
            )
            teams[index]["status"] = "online"
            teams[index]["_id"] = f"team_{item['id']}"

        friendship_list = teams + friends_list
        friendship_list.sort(
            key=lambda x: (x["unread"], x["category"], x["status"] == "online", x["name"]), reverse=True
        )

        logger.info(f"Current number of users: {len(friends_list)}")
        return friendship_list
    except Exception as e:
        raise e
